C:/Projekte/RAIS/FillCustomQueryTableCL/Query.cs

Go to the documentation of this file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Xml;
using RAIS.DataAccessLayer;
namespace FillCustomQueryTableCL
{
    public class Query
    {
        #region Fields
        public static readonly string GetQueryTypes =
                    "SELECT     [PK Query Type ID], " +
                    "   [Query Type Visible Name] " +
                    "FROM [Query Type]";
        protected static Dictionary<string, int> QueryTypes = new Dictionary<string, int>();
        protected static SqlConnection sqlConnection;
        string name;
        int type;
        string text;
        protected List<Parameter> Parameters = new List<Parameter>();
        #endregion

        #region Properties
        public int Type
        {
            get { return type; }
            set { type = value; }
        }

        public string Name
        {
            get { return name; }
            set { name = value; }
        }

        public string Text
        {
            get { return text; }
            set { text = value; }
        }
        #endregion

        public static void InitQuery(string connectionString)
        {
                        if (QueryTypes.Count == 0)
                        {
                                using (sqlConnection = new SqlConnection(connectionString))
                                {
                                        sqlConnection.Open();
                                        using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
                                        {
                                                sqlCommand.CommandText = GetQueryTypes;
                                                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                                                {
                                                        while (reader.Read())
                                                                QueryTypes.Add((string)reader["Query Type Visible Name"], (int)reader["PK Query Type ID"]);
                                                }
                                        }
                                }
                        }
        }

        protected string GetTypeFromSQLType(string typeName)
        {
            string ret;
            switch (typeName)
            {
                case "int":
                    ret = "Integer";
                    break;
                case "datetime":
                    ret = "Date";
                    break;
                case "nvarchar":
                    ret = "Text";
                    break;
                case "float":
                    ret = "Rational";
                    break;
                case "xml":
                    ret = "Xml";
                    break;
                default:
                    ret = "Unknown";
                    break;
            }
            return ret;
        }

        public static Dictionary<string, Query> Create(string connectionString, string fileName)
        {
            Dictionary<string, Query> dic = new Dictionary<string, Query>();

            XmlDocument doc = new XmlDocument();
            doc.Load(fileName);
            XmlNodeList nodes = doc.GetElementsByTagName("Query");
            using (sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                foreach (XmlNode node in nodes)
                {
                    string type = node.Attributes["Type"].InnerText;
                    string name = node.Attributes["Name"].InnerText;
                    switch (type)
                    {
                        case "Consistency Check":
                            dic.Add(name, new ConsistencyCheckQuery(name));
                            break;
                        case "Entry Filter":
                            dic.Add(name, new EntryFilterQuery(name));
                            break;
                        case "Preselection List":
                            dic.Add(name, new PreselectionListQuery(name));
                            break;
                        case "Query":
                            dic.Add(name, new QueryQuery(name));
                            break;
                        case "Statistics":
                            dic.Add(name, new StatisticsQuery(name));
                            break;
                        case "Preselection Filter":
                            dic.Add(name, new PreselectionFilterQuery(name));
                            break;
                        case "RAN Autogeneration":
                            dic.Add(name, new RANSP(name));
                            break;
                        default:
                            dic.Add(name, new OtherQuery(name));
                            break;
                    }
                }
            }
            return dic;
        }

        //public Query() { }
        protected Query(string name, string type)
        {
            this.name = name;
            this.type = QueryTypes[type];
            StringBuilder sb = new StringBuilder();
            using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
            {
                sqlCommand.CommandText = "sp_helptext '" + this.name + "'";
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read())
                        sb.AppendLine((string)reader[0]);
                }
            }
            sb = RemoveComments(sb);
            sb.Replace("&#xD;&#xA;", " ");
            sb.Replace("&#x9;", " ");
            sb.Replace("\r\n\r\n", "\r\n");
            text = GetQuery(sb.ToString());
        }

        protected virtual void PreСorrectParameters(SqlDataReader reder)
        { }

        protected virtual void СorrectParameters()
        { }

        #region Public methods

        public void WriteToXML(XmlWriter query, XmlWriter parameters)
        {
            foreach (Parameter param in Parameters)
                param.WriteToXML(parameters);
            query.WriteStartElement("Query");
            query.WriteAttributeString("QueryName", name);
            query.WriteAttributeString("QueryText", text);
            query.WriteAttributeString("QueryType", type.ToString());
            query.WriteEndElement();
        }
        #endregion

        #region Private methods
        protected virtual string GetQuery(string inputQueru)
        {
            string str = inputQueru.Substring(inputQueru.IndexOf("begin", StringComparison.CurrentCultureIgnoreCase));
            int startIndex = str.IndexOf("select", StringComparison.CurrentCultureIgnoreCase);
            int endIndex = str.IndexOf("return", startIndex, StringComparison.CurrentCultureIgnoreCase);

            str = str.Substring(startIndex, endIndex - startIndex);
            return str;
        }

        private StringBuilder RemoveComments(StringBuilder str)
        {
            StringBuilder ret = new StringBuilder();
            bool inComments = false;
            bool inLineComment = false;
            for (int i = 0; i < str.Length; i++)
            {
                if (!inLineComment && !inComments && str[i] == '/' && str[i + 1] == '*')
                    inComments = true;
                if (!inLineComment && !inComments && str[i] == '-' && str[i + 1] == '-')
                    inLineComment = true;

                if (!inComments && !inLineComment)
                    ret.Append(str[i]);

                if (inLineComment && i - 1 >= 0 && str[i] == '\n' && str[i - 1] == '\r')
                    inLineComment = false;
                if (inComments && i - 1 >= 0 && str[i] == '/' && str[i - 1] == '*')
                    inComments = false;
            }
            return ret;
        }

        private int FindParamByName(string paramName)
        { 
            for (int i=0;i<Parameters.Count;i++)
                if (Parameters[i].Name == paramName)
                    return i;
            return -1;
        }
        #endregion
    }
}